﻿using CallCenter.Data.Base;
using CallCenter.Data.BizUtilities.Base;
using CallCenter.Data.Models;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;


namespace CallCenter.Data.BizUtilities
{
    public class CompanyContactsUtility : BaseUtility
    {
        public CompanyContactsUtility(DataManager manager)
            : base(manager)
        { }

        /// <summary>
        /// Insert to CompanyContacts table
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        public ReturnMessage<CompanyContactsViewModel> InsertToCompanyContact(CompanyContactsViewModel model)
        {
            var returnMessage = new ReturnMessage<CompanyContactsViewModel>();
            //  if(this.ValidateScratchPadModel(model))
            //   {
            var id = this.Insert(model.companyId, model.contactId);
            if (id == -1)
            {
                returnMessage.Message = "We cannot insert data to database";
                returnMessage.Status = false;
            }
            else if (id == -2)
            {
                returnMessage.Message = "Contact Already exist";
            }
            else
            {
                model.id = id;
                returnMessage.Status = true;
            }
            return returnMessage;
        }

        /// <summary>
        /// Update to company contacts
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>

        public ReturnMessage<CompanyContactsViewModel> UpdateToCompanyContact(CompanyContactsViewModel model)
        {
            var returnMessage = new ReturnMessage<CompanyContactsViewModel>();
            //  if(this.ValidateScratchPadModel(model))
            //   {
            var id = this.Update(model.id, model.companyId, model.contactId);
            if (id == -1)
            {
                returnMessage.Message = "We cannot insert data to database";
                returnMessage.Status = false;
            }
            else if (id == -2)
            {
                returnMessage.Message = "Contact already exists";
            }
            else
            {
                model.id = id;
                returnMessage.Status = true;
            }
            return returnMessage;
        }

        /// <summary>
        /// Insert Contact to a company
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>

        public ReturnMessage<AddCompanyToContactVM> InsertCompanyToContact(AddCompanyToContactVM model)
        {
            var returnMessage = new ReturnMessage<AddCompanyToContactVM>();
            //  if(this.ValidateScratchPadModel(model))
            //   {
            var id = this.Insert(model.companyId, model.contactId);
            if (id == -1)
            {
                returnMessage.Message = "We cannot insert data to database";
                returnMessage.Status = false;
            }
            else if (id == -2)
            {
                returnMessage.Message = "Contact Already exist";
                returnMessage.Status = false;
            }
            else
            {
                model.id = id;
                returnMessage.Status = true;
            }
            return returnMessage;
        }

        /// <summary>
        /// Update Company to contact
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>

        public ReturnMessage<AddCompanyToContactVM> UpdateCompanyToContact(AddCompanyToContactVM model)
        {
            var returnMessage = new ReturnMessage<AddCompanyToContactVM>();
            //  if(this.ValidateScratchPadModel(model))
            //   {
            var id = this.Update(model.id, model.companyId, model.contactId);
            if (id == -1)
            {
                returnMessage.Message = "We cannot insert data to database";
                returnMessage.Status = false;
            }
            else if (id == -2)
            {
                returnMessage.Message = "Contact already exists";
            }
            else
            {
                model.id = id;
                returnMessage.Status = true;
            }
            return returnMessage;
        }

        /// <summary>
        /// Get All contacts that attached to company
        /// </summary>
        /// <param name="contactId"></param>
        /// <returns></returns>
        public List<AddCompanyToContactVM> GetContact(int contactId) //Gets all the company data form the contacts table
        {
            string sql = "SELECT compC.companyId, compC.contactId, compC.id, cp.name as company, " +
                 "cp.streetOne + ',' + cp.streetTwo + ',' + cp.city as address, cp.phone from ddlCompanyContacts compC" +
                " inner join ddlCompany cp on cp.id = compC.companyId " +
                " inner join ccContacts ct on ct.id = compC.contactId WHERE compC.contactId = " + contactId +
                " and compC.companyId not in (select top(1) companyId from contactDetail where contactId = '" + contactId + "')";
            return this.Factory.ExecuteObjects<AddCompanyToContactVM>(sql);
        }


        /// <summary>
        /// Get CompanyContacts
        /// </summary>
        /// <param name="companyId"></param>
        /// <returns></returns>
        public DataTable GetContactSet(int companyId) //Gets all the data form the contacts table
        {
            string sql = "SELECT compC.contactId" +
                  " from ddlCompanyContacts compC " +
                  " WHERE compC.companyId = " + companyId;

            return this.Factory.ExecuteTable(sql);
        }


        /// <summary>
        /// Get contacts to fill company contact grid
        ///
        /// </summary>
        /// <returns></returns>
        public DataTable GetAllGrid() //Gets all the data form the contacts table
        {
            string sql = "SELECT compC.companyId, compC.contactId, compC.id, cp.name as company, compC.emailInvoice, " +
                " ct.firstName+' '+ct.lastName as contact from ddlCompanyContacts compC " +
                " inner join ddlCompany cp on cp.id = compC.companyId " +
                " inner join ccContacts ct on ct.id = compC.contactId";

            return this.Factory.ExecuteTable(sql);
        }


        /// <summary>
        /// Get AllCompany based on companyId
        /// </summary>
        /// <param name="companyId"></param>
        /// <returns></returns>
        public List<CompanyContactsViewModel> GetAllCompany(int companyId) //Gets all the data form the contacts table
        {
            string sql = "SELECT compC.companyId, compC.contactId, compC.id, (select name from ddlCompany where id = ct.companyId) as company, " +
                "  ct.lastname+','+ ct.firstname as name, ct.id as ctId, ct.businessPhone, ct.mobilePhone, ct.email, " +
                " ct.jobTitle,ct.department from ddlCompanyContacts compC " +
                " inner join ddlCompany cp on cp.id = compC.companyId " +
                " inner join ccContacts ct on ct.id = compC.contactId WHERE compC.companyId = " + companyId;
            return this.Factory.ExecuteObjects<CompanyContactsViewModel>(sql);

        }

      /// <summary>
      /// Insert into contact table
      /// </summary>
      /// <param name="companyId"></param>
      /// <param name="contactId"></param>
      /// <returns></returns>
        public int Insert(int companyId, int contactId) //insert data to a contact table
        {
            int nRtn = -1;
            string sql = "INSERT INTO ddlCompanyContacts (companyId, contactId)" +
                                            " Values(@companyId, @contactId) SELECT @pResult =@@IDENTITY";

            using (var cmd = this.Factory.GetCommand(sql))
            {
                this.Factory.AddParameter(cmd, "@companyId", SqlDbType.Int, 10, companyId);
                this.Factory.AddParameter(cmd, "@contactId", SqlDbType.Int, 10, contactId);


                try
                {
                    var param = this.Factory.AddParameter(cmd, "@pResult", System.Data.SqlDbType.NVarChar, 500, null);
                    param.Direction = System.Data.ParameterDirection.Output;
                    this.Factory.ExecuteNonQuery(cmd);

                    nRtn = Convert.ToInt32(param.Value);
                }
                catch (Exception err)
                {
                    String strErr = err.Message;
                    return -2;
                }

                finally
                {

                }

                return nRtn; //retruning the data stored in the table
            }
        }

        /// <summary>
        /// Insert into CompanyContacts table
        /// </summary>
        /// <param name="commId"></param>
        /// <param name="contactid"></param>
        /// <param name="date"></param>
        /// <returns></returns>

        public int InsertToComContact(int commId, int contactid, DateTime date)
        {
            int nRtn = -1;

            string sql = "Insert INTO ccCommcontact " +
                 "(commId, contactId, date) VALUES(@commId, @contactId, @date)  SELECT @pResult =@@IDENTITY ";



            using (var cmd = this.Factory.GetCommand(sql))
            {
                this.Factory.AddParameter(cmd, "@commId", SqlDbType.Int, 10, commId);
                this.Factory.AddParameter(cmd, "@contactId", SqlDbType.Int, 10, contactid);
                this.Factory.AddParameter(cmd, "@date", SqlDbType.SmallDateTime, 10, date);
                {
                    var param = this.Factory.AddParameter(cmd, "@pResult", System.Data.SqlDbType.NVarChar, 500, null);
                    param.Direction = System.Data.ParameterDirection.Output;

                    this.Factory.ExecuteNonQuery(cmd);
                    nRtn = Convert.ToInt32(param.Value);
                }


                return nRtn; //retruning the data stored in the table
            }
        }

        /// <summary>
        /// Update CompanyContacts table
        /// </summary>
        /// <param name="id"></param>
        /// <param name="companyId"></param>
        /// <param name="contactId"></param>
        /// <returns></returns>
        public int Update(int id, int companyId, int contactId) //insert data to a contact table
        {
            int nRtn = -1;
            string sql = "UPDATE ddlCompanyContacts SET companyId=@companyId, contactId=@contactId WHERE id=id";

            using (var cmd = this.Factory.GetCommand(sql))
            {
                this.Factory.AddParameter(cmd, "@companyId", SqlDbType.Int, 10, companyId);
                this.Factory.AddParameter(cmd, "@contactId", SqlDbType.Int, 10, contactId);
                this.Factory.AddParameter(cmd, "@ID", SqlDbType.Int, 10, id);



                return this.Factory.ExecuteNonQuery(cmd);
            }

            //  return nRtn;
        }


        /// <summary>
        /// Delete from company contacts table
        /// </summary>
        /// <param name="comConts"></param>
        public void Delete(int comConts) //delete the row of data using the id passed to the function
        {
            string sql = "DELETE FROM ddlCompanyContacts " +
                                    "WHERE id = @ID ";

            using (var cmd = this.Factory.GetCommand(sql))
            {

                this.Factory.AddParameter(cmd, "@ID", SqlDbType.Int, 10, comConts);
                this.Factory.ExecuteNonQuery(cmd);
            }

        }


   /// <summary>
   /// Get All companyContacts
   /// </summary>
   /// <returns></returns>
        public List<CompanyContactsViewModel> GetCompanyContacts()
        {
            string sql = "SELECT id, lastname+', '+firstname as name FROM ccContacts WHERE id not in (SELECT contactId from ddlEmployees)";

            return this.Factory.ExecuteObjects<CompanyContactsViewModel>(sql);
        }
        /// <summary>
        /// Get AllContactsCompany
        /// </summary>
        /// <returns></returns>

        public List<CompanyContactsViewModel> GetAllContactsCompany()
        {
            //string sql = "select A.lastname+','+ A.firstname as name,C.name as companyname from ccContacts A Left join ddlCompanyContacts B on A.id=B.contactId inner join ddlCompany C on c.id=B.companyId where B.IsInActive is null";
            string sql = "select A.lastname+','+ A.firstname as name,C.name as companyname from ccContacts A inner join ddlCompany C on c.id=A.companyId ";

            return this.Factory.ExecuteObjects<CompanyContactsViewModel>(sql);
        }


      /// <summary>
      /// Update companycontacttoActive
      /// </summary>
      /// <param name="companyId"></param>
      /// <param name="contactId"></param>

        internal void UpdateIsInActive(int companyId, int contactId)
        {
            string sql = "UPDATE ddlCompanyContacts SET IsInActive= 1 where companyId <> @companyId and contactId=@contactId";
            using (var cmd = this.Factory.GetCommand(sql))
            {
                this.Factory.AddParameter(cmd, "@companyId", SqlDbType.Int, 10, companyId);
                this.Factory.AddParameter(cmd, "@contactId", SqlDbType.Int, 10, contactId);

                this.Factory.ExecuteNonQuery(cmd);
            }

        }
    }
}
